CS157a Fall 2018Practice Midterm 1

Studying for one of my tests does involve some memorization. I believe this is an important skill. Often people waste a lot of time and fail to remember the things they are trying to memorize. Please use a technique that has been shown to work such as the method of loci. Other memorization techniques can be found off the Wiki Page for Moonwalking with Einstein. Given this, to study for the midterm I would suggest you:

• Know how to do (by heart) all the practice problems.
• Go over your notes at least three times. Second and third time try to see how much you can remember from the first time.
• Go over the homework problems.
• Try to create your own problems similar to the ones I have given and solve them.
• Skim the relevant sections from the book.
• If you want to study in groups, at this point you are ready to quiz each other.

The practice midterm is below. Here are some facts about the actual midterm: (a) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (b) You should bring photo ID. (c) There will be more than one version of the test. Each version will be of comparable difficulty. (d) One problem (less typos) on the actual test will be from the practice test.

1. Briefly describe what a resource manager and a buffer manager of a database do.
2. What are the ACID properties of a DBMS transaction? Briefly describe each.
3. Define data model and give the three facets that any data model needs to specify.
4. Give the SQL command necessary to create a table for the relation R(A:int, B:int, C:char(5)) where `A, B` was the primary key. Give the SQL need to insert a row into this table. Give the SQL needed to remove this table from the database.
5. Give examples of the following: (a) two tables each of at lest 3 rows, whose natural join is equal to their Cartesian product, (b) two tables with 3 rows each whose join is 1 row, (c) a join with a dangling tuple.
6. Express the following queries in the relational algebra: (a) All movies that have at least two stars in them, (b) all producers who had two movies in 1977, (c) Movies names that occur before 1950 that also occur after 1950.
7. Define the notion of referential integrity constraint. Explain how a referential integrity constraint can be defined as a relational algebra constraint.
8. Use the closure algorithm from class to show step by step how to compute `{A, B}^+` assuming we have attributes `A,B,C,D,E` and FDs `BC-> AD`, `A->C`, `AD -> E`.
9. Suppose we have the relation `R(A, B, C, D)` on which the following FDs hold: `{A->BC, C->D, B->A}`. Decompose `R` into a DB schema into BCNF using the algorithm from class showing important intermediate steps.
10. Suppose we decompose `R(A, B, C, D)`, on which the following FDs hold: `{A->BC, C->D, B->A}`, into `R_1(A,B)`, `R_2(A,B,C)`, `R_3(C,D)`. Use the chase algorithm to determine if this decomposition has the lossless join property.